<?php
/**
 * Pipahr
 *
 * @author Wei Ma
 * @author Randy Zhao
 * @copyright 2013 pipahr.com
 */

JTable::addIncludePath(JPATH_ADMINISTRATOR.'/components/com_puser/tables');
JTable::addIncludePath(JPATH_ADMINISTRATOR.'/components/com_jobs/tables');
JLoader::register("PResumesModelResume", OC::$SERVERROOT."/apps/resumes/lib/resume.php");
/**
 * Registration controller class for Users.
 */
class PInterviewModelInterview extends JModelLegacy
{ 
   static $validGiveUpReasons = array("对该职位无兴趣", "已找到工作","未投递简历","不打算换工作","其他原因放弃");
   
   protected function validateFields($fields=array()) {
      foreach($fields as $field => $value) {
         switch($field) {
            case 'email':
               if(!JMailHelper::isEmailAddress($value)) {
                  $this->setErrorCode(40022);
                  $this->setError("请输入正确格式的电子邮件");
                  return false;
               }
            break;
            case 'phone':
               if(strlen($value) > 0 && !JobHelper::isPhone($value)) {
                  $this->setErrorCode(40023);
                  $this->setError("请输入正确的联系电话");
                  return false;
               }
            break;
         }
      }
      return true;
   }   
   
   /**
    * Function getInfo
    * @param  string $token This is what we get from url
    * @param  string $email This is what we get from url
    * @return object        Company & user info object
    *
    * Get some info for almost all page
    */
   public function getInfo($token, $email=null) {
      if (empty($token))
         return null;

      $db = PFactory::getDbo();

      $condition = $email === null ? "" : "AND hj.email = ".$db->quote($email);
      $query = "SELECT je.comp_name,je.validated,jjc.category,jct.comp_type,
         jcst.size_type,c.state AS cstate,c.city AS ccity,je.contact_name,
         jj.email,jj.phone,c.address,jj.job_title,jj.state,jj.city,jst.salary_type,
         jje.exp_name,jj.num_needed,hj.name AS jobseeker_name,hj.email AS jobseeker_email,
         hj.phone AS jobseeker_phone, hj.source,hj.job_id,hj.online_interview_progress,jj.employer_id,hjt.jobseeker_id,
         hjt.giveup,c.avatar,c.avatarapproved, jid.industry, je.sub_domain
         FROM *PREFIX*hr_jobseeker_token hjt
         LEFT JOIN *PREFIX*hr_jobseeker hj ON hjt.jobseeker_id = hj.id
         LEFT JOIN #__jobs_job jj ON hj.job_id = jj.id
         LEFT JOIN #__jobs_employer je ON jj.employer_id = je.user_id
         LEFT JOIN #__comprofiler c ON jj.employer_id = c.user_id
         LEFT JOIN #__jobs_job_categ jjc ON jj.id_job_spec_categ = jjc.id
         LEFT JOIN #__jobs_comp_type jct ON je.id_comp_type = jct.id
         LEFT JOIN #__jobs_comp_size_type jcst ON je.id_size_type = jcst.id
         LEFT JOIN #__jobs_salary_type jst ON jj.id_salary_type = jst.id
         LEFT JOIN #__jobs_job_exp jje ON jj.id_job_exp = jje.id
         LEFT JOIN #__jobs_industry jid ON je.id_industry = jid.id
         WHERE hjt.token=".$db->quote($token)." ".$condition;
         // Remove AND jj.is_active = 'y' in sql for now
      $db->setQuery(PUtil::replaceOCPrefix($query));
      return $db->loadObject();
   }

   /**
    * Function getJobseekerBasicInfo
    * @param  string $token This is what we get from url
    * @return object        Company & user info object
    *
    * Get some info for almost all page
    */
   public function getJobseekerBasicInfo($token) {
      if (empty($token))
         return null;

      $db = PFactory::getDbo();

      $query = "SELECT j.id as jobseeker_id, j.name AS jobseeker_name,j.email AS jobseeker_email
         FROM *PREFIX*hr_jobseeker_token t
         LEFT JOIN *PREFIX*hr_jobseeker j ON t.jobseeker_id = j.id
         WHERE t.token=".$db->quote($token);
      $db->setQuery(PUtil::replaceOCPrefix($query));
      return $db->loadObject();
   }

   /**
    * Function verifyToken
    * @param  array $post Jobseeker's input validation infomation
    * @param  bool  $is_mobile Set true if called by mobile api
    * @return bool
    *
    * Validate the visitor has authority to access online test use this token
    */
   public function verifyToken($post, $is_mobile=false) {
      /* comment out to avoid session time out issue 
         if (!$is_mobile && !PSession::checkToken('post')) {
         $this->setError('非法的系统安全码');
         return false;
      }
      */
      if (empty($post)) {
         $this->setErrorCode(10002);
         $this->setError('网络错误, 请重试');
         return false;
      }

      $token = JRequest::getString('token','');
      $email = JRequest::getString('email','');
      $phone = JRequest::getString('phone','');

      if (empty($email) && empty($phone)) {
         $this->setErrorCode(40024);
         $this->setError('验证失败，请核对您简历上提供的信息');
         return false;
      }

      $db = PFactory::getDbo();
      $vcode = JRequest::getString("vcode", "");
      $app =  PFactory::getApplication();
      // do we already have this token's phone and email?
      $query = "SELECT hj.id,hj.phone,hj.email,hj.job_id,hj.company_id
         FROM *PREFIX*hr_jobseeker_token hjt
         LEFT JOIN *PREFIX*hr_jobseeker hj ON hjt.jobseeker_id = hj.id
         WHERE hjt.token=".$db->quote($token);
      $db->setQuery(PUtil::replaceOCPrefix($query));
      $info = $db->loadObject();

      /**
       * set up where sql, there is no chance we dont have none of these info
       * set up update sql, if we only have one of these two info, we should grap the other one just for us
       */
      $where = "";
      $updatesql = "";
      if (empty($info->phone)) {
         if(!$this->validateFields(array('phone'=>$post['phone'])))
            return false;
         $where = "AND hj.email=".$db->quote($email);
         $updatesql = "UPDATE *PREFIX*hr_jobseeker SET phone=".$db->quote($phone)." WHERE id='".intval($info->id)."'";
      } elseif ((empty($info->email) || isFakeEmail($info->email)) && !empty($email)) {
         if(!$this->validateFields(array('email'=>$email)))
            return false;
         //check vcode
         if(!$is_mobile && !isDevMode() && !JobHelper::checkEmailVcode($vcode,$post['email'])) {
            $this->setError("错误的邮箱验证码");
            return false;
         }

         $where = "AND hj.phone=".$db->quote($phone);
         // This place need check jobseeker is already exist,or cannot update cause sql error
         $model = new PResumesModelResume();
         if(!$model->checkResumeExists($email,$info->job_id,'',$info->id)){
            $updatesql = "UPDATE *PREFIX*hr_jobseeker SET email=".$db->quote($email)." WHERE id='".intval($info->id)."'";
         }
      } elseif((empty($info->email) || isFakeEmail($info->email)) && empty($email)){
         $where = "AND hj.phone=".$db->quote($phone);
      }else {
         if (empty($email)) {
            $where = "AND hj.phone=".$db->quote($phone);
         } elseif (empty($phone)) {
            $where = "AND hj.email=".$db->quote($email);
         } else {
            $where = "AND hj.email=".$db->quote($email)." AND hj.phone=".$db->quote($phone);
         }
      }
      // Check authority
      $query = "SELECT COUNT(*)
         FROM *PREFIX*hr_jobseeker_token hjt
         LEFT JOIN *PREFIX*hr_jobseeker hj ON hjt.jobseeker_id = hj.id
         WHERE hjt.token=".$db->quote($token)." ".$where;
      $db->setQuery(PUtil::replaceOCPrefix($db->useLastConnection($query)));
      $count = $db->loadResult();
      if ($count <= 0) { // Can't find match
         $this->setErrorCode(40024);
         $this->setError('验证失败，请核对您简历上提供的信息。');
         return false;
      }

      if (!empty($updatesql)) {
         $db->setQuery(PUtil::replaceOCPrefix($updatesql));
         $db->execute();
      }
      $updatesql = "UPDATE *PREFIX*hr_jobseeker_token SET lastvisit_date = UTC_TIMESTAMP() WHERE token=".$db->quote($token);
      $db->setQuery(PUtil::replaceOCPrefix($updatesql));
      $db->execute();

      return true;
   }

   /**
    * Function getInterviewQuestions
    * @param  int   $job_id  Job id
    * @param  bool  $is_mobile Set true if called by mobile api
    * @return array
    */
   public function getInterviewQuestions($job_id, $is_mobile=false) {
      if (empty($job_id))
         return null;
      $db = PFactory::getDbo();

      // Get all default question
      if ($is_mobile) {
         $select = " hiq.id, hiq.alias, hiq.text_jobseeker, hiq.choices, hiq.match_rule, hiqu.unit_name AS unit ";
      } else {
         $select = " hiq.id AS iid, hiq.alias, hiq.text_jobseeker AS text, hiq.choices, hiq.match_rule, hiqu.unit_name ";
      }
      $query = "SELECT ".$select."
         FROM *PREFIX*hr_interview_questions hiq
         LEFT JOIN *PREFIX*hr_interview_questions_unit hiqu ON hiqu.id = hiq.unit_id
         WHERE hiq.alias NOT IN ('LANG','SKILL','LOCATION') ORDER BY hiq.id ASC";
      $db->setQuery(PUtil::replaceOCPrefix($query));
      $default_ques = $db->loadObjectList();

      // Get all custom question
      if ($is_mobile) {
         $select = " hiq.id, hiq.alias, hiq.text_jobseeker, hiq.choices, hiq.match_rule, hiqu.unit_name AS unit, hci.expected_answer ";
      } else {
         $select = " hiq.id AS iid, hiq.alias, hiq.text_jobseeker AS text, hiq.choices, hiq.match_rule, hiqu.unit_name, hci.expected_answer ";
      }
      $query = "SELECT ".$select."
         FROM *PREFIX*hr_company_interview hci
         LEFT JOIN *PREFIX*hr_interview_questions hiq ON hci.interview_id = hiq.id
         LEFT JOIN *PREFIX*hr_interview_questions_unit hiqu ON hiqu.id = hiq.unit_id
         WHERE hiq.alias IN ('LANG','SKILL','LOCATION') AND hci.job_id = ".intval($job_id)." ORDER BY hiq.id ASC";
      $db->setQuery(PUtil::replaceOCPrefix($query));
      $custom_ques = $db->loadObjectList();

      if (!empty($custom_ques)) {
         foreach ($custom_ques as $k => $v) {
            array_push($default_ques, $v);
         }
      }

      return $default_ques;
   }

   /**
    * Function getJobseekerInfo
    * @param  string $token User's token
    * @return object
    */
   public function getJobseekerInfo($token) {
      if (empty($token))
         return null;
      $db = PFactory::getDbo();
      $query = "SELECT hj.highest_education_degree_level AS degree, hj.dateofbirth, hj.sex, hj.workexp_months, MIN(s.level) AS level
         FROM *PREFIX*hr_jobseeker hj
         LEFT JOIN *PREFIX*hr_jobseeker_token hjt ON hjt.jobseeker_id = hj.id
         LEFT JOIN *PREFIX*hr_jobseeker_education hje ON hje.jobseeker_id = hj.id
         LEFT JOIN #__schoollist s ON s.schoolid = hje.school_id
         WHERE s.level > 0 AND hjt.token = ".$db->quote($token);
      $db->setQuery(PUtil::replaceOCPrefix($query));
      $result = $db->loadObject();
      if (!empty($result->sex) && $result->sex == "M") {
         $result->sex = "1";
      } elseif (!empty($result->sex) && $result->sex == "F") {
         $result->sex = "2";
      }
      return $result;
   }

   /**
    * Function postinterview
    * @param  array $post Interview answer post array
    * @param  bool  $is_mobile Set true if called by mobile api
    * @return bool
    */
   public function postinterview($post, $is_mobile=false) {
      if (!$is_mobile && !PSession::checkToken('post')) {
         $this->setError('验证已失效，请重新验证');
         return false;
      }

      if (empty($post)) {
         $this->setErrorCode(10002);
         $this->setError('非正常提交！');
         return false;
      }

      // session may record failed
      /*if (empty($post['session_id'])) {
         $this->setError('网络异常，请重试！');
         return false;
      }*/

      $db = PFactory::getDbo();
      // stay on master: read-your-write 
      $db->stayOnMaster(); 

      $token = JRequest::getString('token','');
      $company_id = JRequest::getInt('employer_id',0);
      $job_id = JRequest::getInt('job_id',0);
      $jobseeker_id = JRequest::getInt('jobseeker_id',0);
      $session_id = JRequest::getInt('session_id',0);

      if ($is_mobile) {
         $answers = isset($post['answers']) ? $post['answers'] : array();
         $workexp = isset($answers['WORK_EXP']) ? $answers['WORK_EXP'] : 0;
         $workexpmonths = isset($answers['WORK_EXP_MONTHS']) ? $answers['WORK_EXP_MONTHS'] : 0;
      } else {
         $answers = $post;
         $workexp = JRequest::getInt('WORK_EXP',0);
         $workexpmonths = JRequest::getInt('WORK_EXP_MONTHS',0);
      }

      if (empty($answers)) {
         $this->setErrorCode(40014);
         $this->setError('请认真填写所有面试题！');
         return false;
      }

      $interview_stats = PInterviewHelper::getInterviewProgress($jobseeker_id, $company_id);
      if (isset($interview_stats['interview']) && $interview_stats['interview']) {
         $db->leaveOnMaster();
         $this->setErrorCode(40002);
         $this->setError('您已参加过面试，不可重复提交！');
         return false;
      }

      // update session registry now
      /*if(!$this->setAuthInfo($session_id, $token)) {
        $this->setError('网络异常，请重试！');
        return false;
      }*/

      // Calculate work_exp
      // one cannot have work_exp more than 50 years in practice 
      $workexp_totalmonths = $workexp * 12 + $workexpmonths;
      if($workexp_totalmonths > 50 * 12) {
         $db->leaveOnMaster();
         $this->setErrorCode(40012);
         $this->setError('您的工作经验年限太大了，请认真填写！');
         return false;
      }
      if ($workexp_totalmonths <= 0) {
         $workexp_summary = "";
      } else {
         if ($workexp_totalmonths >= 12) {
            $workexp_summary = $workexp_totalmonths%12 > 0 ? floor($workexp_totalmonths/12)."年".intval($workexp_totalmonths%12)."个月" : floor($workexp_totalmonths/12)."年";
         } else {
            $workexp_summary = intval($workexp_totalmonths)."个月";
         }
      }

      $answers['WORK_EXP'] = $workexp_totalmonths;

      // update jobseeker's workexp_months
      $update = "UPDATE *PREFIX*hr_jobseeker SET
                 workexp_summary = ".$db->quote($workexp_summary).",
                 workexp_months = '".intval($workexp_totalmonths)."'
                 WHERE id =".intval($jobseeker_id);
      $db->setQuery(PUtil::replaceOCPrefix($update));
      $db->execute();

      CacheHelper::triggerRemove($jobseeker_id, array('*PREFIX*hr_jobseeker','*PREFIX*hr_jobseeker-'.$jobseeker_id));

      // Handle skill and lang and addskill separately
      $langs = isset($answers['LANG']) ? $answers['LANG'] : array();
      $skills = isset($answers['SKILL']) ? $answers['SKILL'] : array();
      $skilllevels = isset($answers['SKILL_LEVEL']) ? $answers['SKILL_LEVEL'] : array();
      $addskillnames = isset($answers['MORESKILL_NAME']) ? $answers['MORESKILL_NAME'] : array();
      $addskilllevels = isset($answers['MORESKILL_LEVEL']) ? $answers['MORESKILL_LEVEL'] : array();
      $addskillexpes = isset($answers['MORESKILL_EXP']) ? $answers['MORESKILL_EXP'] : array();

      // Unset some key , left answers only
      if ($is_mobile) {
         unset($answers['WORK_EXP_MONTHS'],$answers['SKILL'],$answers['SKILL_LEVEL'],$answers['LANG'],$answers['MORESKILL_NAME'],$answers['MORESKILL_LEVEL'],$answers['MORESKILL_EXP']);
      } else {
         unset($answers['token'],$answers['employer_id'],$answers['job_id'],$answers['jobseeker_id'],$answers['requesttoken'],$answers['session_id'],$answers['WORK_EXP_MONTHS'],$answers['SKILL'],$answers['LANG'],$answers['MORESKILL_NAME'],$answers['MORESKILL_LEVEL'],$answers['MORESKILL_EXP'],$answers['SKILL_LEVEL']);
      }

      // Calculate age
      if (!empty($answers['AGE'])) {
         list($birth_y,$birth_m,$birth_d) = explode('-',$answers['AGE']);
         $current_m = date('n');
         $current_d = date('j');
         $answers['AGE'] = date('Y') - $birth_y - 1;
         if ($current_m > $birth_m || $current_m == $birth_m && $current_d > $birth_d)
            $answers['AGE'] ++;
      }

      if (empty($answers) && empty($langs) && empty($skills)) {
         $db->leaveOnMaster();
         $this->setErrorCode(10002);
         $this->setError('非正常提交！');
         return false;
      }

      if (empty($job_id)) {
         $db->leaveOnMaster();
         $this->setErrorCode(10002);
         $this->setError('网络异常，请重试！');
         return false;
      } else {
         // Get all default question
         $query = "SELECT hiq.id, hiq.alias
            FROM *PREFIX*hr_interview_questions hiq
            WHERE hiq.alias NOT IN ('LANG','SKILL','LOCATION') AND hiq.alias != 'MORESKILL'";
         $db->setQuery(PUtil::replaceOCPrefix($query));
         $questions = $db->loadAssocList();

         // Get all custom question
         $query = "SELECT hiq.id, hiq.alias
            FROM *PREFIX*hr_company_interview hci
            LEFT JOIN *PREFIX*hr_interview_questions hiq ON hci.interview_id = hiq.id
            WHERE hiq.alias IN ('LANG','SKILL','LOCATION') AND hci.job_id = ".intval($job_id)." AND hiq.alias != 'MORESKILL'";
         $db->setQuery(PUtil::replaceOCPrefix($query));
         $custom_ques = $db->loadAssocList();

         if (!empty($custom_ques)) {
            foreach ($custom_ques as $k => $v) {
               array_push($questions, $v);
            }
         }
      }

      if (count($questions) != (count($answers) + count($langs) + count($skills))) {
         $db->leaveOnMaster();
         $this->setErrorCode(10002);
         $this->setError('网络异常，请重试！');
         return false;
      }

      foreach ($answers as $k => $v) {
         if ($k == 'SALARY' && empty($v)) {
            $db->leaveOnMaster();
            $this->setErrorCode(40013);
            $this->setError('请认真填写期望月薪！');
            return false;
         }
         if ($v == null && $k != 'WORK_EXP') {
            $db->leaveOnMaster();
            $this->setErrorCode(40014);
            $this->setError('请认真填写所有面试题！');
            return false;
         }
      }

      $answersArr = $addskillarray = array();

      // insert addskills
      $addskillarray = array_filter($addskillnames, 'strlen');
      if(!empty($addskillarray)){
         $query = "SELECT id FROM `*PREFIX*hr_interview_questions` WHERE alias = 'MORESKILL'";
         $db->setQuery(PUtil::replaceOCPrefix($query));
         $moreskillid = $db->loadResult();

         $queries_skills = $queries_skills_expectedanswers = $queries_skills_levels  =  array();
         foreach($addskillnames as $k=>$v){
            foreach($addskilllevels as $ke => $val){
               if($ke == $k){
                  $queries_skills_levels[$v] = $val;
                  $moreskillinterviewrid[$v] = $moreskillid;
               }
            }

            foreach($addskillexpes as $key=>$value){
               if($key == $k){
                  if(empty($value) || intval($value)<0){
                     $this->setErrorCode(40015);
                     $this->setError('新增'.$v.'技能的工作经验（月）格式有误！');
                      return false;
                  }elseif(intval($value) > 50 * 12){
                     $this->setErrorCode(40016);
                     $this->setError('新增'.$v.'技能的工作经验太大了，请认真填写！！');
                     return false;
                  }else{
                     $queries_skills_expectedanswers[$v] = intval($value);
                  }
               }
            }
         }

         $addskillanswer = array();
         foreach($queries_skills_expectedanswers as $k=>$v){
            foreach($queries_skills_levels as $key => $val){
               if(empty($val)){
                  $this->setErrorCode(40017);
                  $this->setError('新增'.$k.'技能的熟练度为必选项！');
                  return false;
               }elseif($key == $k){
                  $addskillanswer['skill'] = $k;
                  $addskillanswer['level'] = $val;
                  $addskillanswer['exp'] = $v;
                  $addskillanswerjson = json_encode($addskillanswer);
                  $answersArr[$k.$moreskillid] = array('alias'=>'MORESKILL','answer'=>$addskillanswerjson);
               }
            }
         }
      }

      foreach ($questions as $v) {
         if ( isset($answers[$v['alias']]) && $answers[$v['alias']] != null )
            $each_answer = $answers[$v['alias']];
         else
            $each_answer = 0;

         $answersArr[$v['id']] = array('alias'=>$v['alias'],'answer'=>$each_answer);
      }
      // Handle langs
      if (!empty($langs)) {
         foreach ($questions as $v) {
            if ( isset($langs[$v['id']]) && $langs[$v['id']] != null ) {
               $each_answer = $langs[$v['id']];
               $answersArr[$v['id']] = array('alias'=>$v['alias'],'answer'=>$each_answer);
            }
         }
      }
      // Handle skills
      $eachanswer = array();
      if (!empty($skills) &&!empty($skilllevels)) {
         foreach ($questions as $v) {
            if ( isset($skills[$v['id']]) && $skills[$v['id']] != null && isset($skilllevels[$v['id']]) && $skilllevels[$v['id']] != null) {
               $eachanswer['level'] = $skilllevels[$v['id']];
               $eachanswer['exp'] = $skills[$v['id']];
               if($eachanswer['exp'] > 50 * 12){
                  $this->setErrorCode(40018);
                  $this->setError('技能经验太大了，请认真填写！');
                  return false;
               }
               $eachanswerjson = json_encode($eachanswer);

               $answersArr[$v['id']] = array('alias'=>$v['alias'],'answer'=>$eachanswerjson);
            }
         }
      }

      $valuesArr = array();
      foreach ($answersArr as $interview_id => $v) {
         // NOTE: The answer here may not just be int in the future
         if ($v['alias'] == "MORESKILL") {
            $interview_id = $moreskillid;
            $final_answer = $v['answer'];
         }elseif ($v['alias'] == "SKILL") {
            $final_answer = $v['answer'];
         }elseif ($v['alias'] != "CUSTOM") {
            $final_answer = abs(intval($v['answer']));
         }
         $valuesArr[] = "('".intval($company_id)."', '".intval($interview_id)."', '".intval($jobseeker_id)."', ".$db->quote($final_answer).", UTC_TIMESTAMP())";
      }

      $valuesSql = implode(",", $valuesArr);

      if (empty($valuesSql)) {
         $this->setErrorCode(10002);
         $this->setError('网络异常，请重试！');
         return false;
      }

      $query = "INSERT INTO *PREFIX*hr_jobseeker_interview
         (`company_id`, `interview_id`, `jobseeker_id`, `answer`, `answer_time`)
         VALUES ".$valuesSql;
      $db->setQuery(PUtil::replaceOCPrefix($query));
      if (!$db->execute()) {
         $db->leaveOnMaster();
         $this->setErrorCode(10002);
         $this->setError('网络异常，请重试！');
         return false;
      }

      // Update matching level
      if (PInterviewHelper::updateMatchDegree($token, $jobseeker_id, $job_id) === false) {
         $db->leaveOnMaster();
         $this->setErrorCode(10002);
         $this->setError('网络异常，请重试！');
         return false;
      }
      /* update online_interview_progress; basic hr question 50% done  */
      PInterviewHelper::updateInterviewProgress('interview', $jobseeker_id, $job_id);
      $this->removeGiveup($jobseeker_id, $company_id);
      $this->updateJobseekerSkills($jobseeker_id);
      // leave master now
      $db->leaveOnMaster();
      return true;
   }

   public function updateJobseekerSkills($jobseekerId, $skillLimit=6) {
      $db = PFactory::getDbo();
      $query = "SELECT q.id , q.text_hr , jsi.answer , q.choices , u.unit_name , js.company_id
         FROM *PREFIX*hr_jobseeker_interview as jsi
         INNER JOIN *PREFIX*hr_jobseeker AS js ON jsi.jobseeker_id = js.id
         INNER JOIN *PREFIX*hr_interview_questions AS q ON q.id = jsi.interview_id
         LEFT JOIN *PREFIX*hr_interview_questions_unit AS u ON u.id = q.unit_id
         WHERE js.id = " . intval($jobseekerId) . " AND (q.alias = 'SKILL' OR q.alias = 'MORESKILL') ORDER BY q.id DESC limit $skillLimit";
      $db->setQuery(PUtil::replaceOCPrefix($query));
      $result = $db->loadAssocList();
      if(empty($result)) {
         return false;
      }
      $skills = array();$i=0;
      foreach ($result as $info) {
         if(empty($info["answer"])) {continue;}
         $info["answer"] = json_decode($info["answer"], true);
         $info["choices"] = json_decode($info["choices"], true);
         $skills[] = ($info["text_hr"]?$info["text_hr"]:$info["answer"]["skill"]).
         " ".$info["choices"][$info["answer"]["level"]]." ".$info["answer"]["exp"].
         ($info["unit_name"]?"个".$info["unit_name"]:"个月");
      }
      if(empty($skills)) {return false;}
      $skillsStr = implode(" | ", $skills);
      $query = "UPDATE *PREFIX*hr_jobseeker SET workexp_skills = " . $db->quote($skillsStr) . " where id = " . intval($jobseekerId);
      $db->setQuery(PUtil::replaceOCPrefix($query));
      $db->execute();
      CacheHelper::triggerRemove($jobseekerId, array('*PREFIX*hr_jobseeker','*PREFIX*hr_jobseeker-'.$jobseekerId));

      return true;
   }

   public function removeGiveup($jobseekerid,$owner){
      $db = PFactory::getDbo();
      PResumesHelper::setResumeStatus($jobseekerid, 1);
      //if jobseeker give up before on the same jobpositon,now update to not  give up status	
      $query="UPDATE *PREFIX*hr_jobseeker_token  SET giveup=0,giveup_date=UTC_TIMESTAMP() WHERE jobseeker_id=".intval($jobseekerid);
      $db->setQuery(PUtil::replaceOCPrefix($query));
      $db->execute();
      CacheHelper::triggerRemove($jobseekerid, array('*PREFIX*hr_jobseeker_token','*PREFIX*hr_jobseeker_token-'.$jobseekerid));

      $query="DELETE FROM *PREFIX*hr_jobseeker_tag_term_details WHERE jobseeker_id=".intval($jobseekerid). " AND owner=".intval($owner) . " AND tagid IN (SELECT id FROM *PREFIX*hr_jobseeker_tag_term WHERE name IN ('".implode("','",self::$validGiveUpReasons)."') AND owner=".intval($owner).")"; 
      $db->setQuery(PUtil::replaceOCPrefix($query));
      $db->execute();   
      return true;
   }
   /**
    * Function getBaseExamInfo
    * @param  string $job_id Job id
    * @param  string $token User token
    * @return object Base exam info
    */
   public function getBaseExamInfo($job_id, $token="") {
      if (empty($job_id))
         return null;
      $db = PFactory::getDbo();

      // get sheetid
      $query = "SELECT js.id AS sheetid, js.totaltime, jjs.jobid, js.questioncount AS count
         FROM #__jobtest_sheet js
         LEFT JOIN #__jobtest_jobsheet jjs ON jjs.sheetid = js.id
         WHERE jjs.jobid=".intval($job_id)." ORDER BY js.id LIMIT 1";
      $db->setQuery($query);
      $result = $db->loadObject();

      return $result;
   }

   /**
    * Function getExamQuestions
    * @param  integer  $job_id    This job id
    * @param  integer  $sheetid   Sheetid
    * @param  integer  $start     Sql start
    * @param  integer  $limit     Sql limit
    * @param  bool     $is_mobile Set true if called by mobile api
    * @return  array             Paper content
    */
   public function getExamQuestions($job_id, $sheetid, $start=0, $limit=5, $is_mobile=false) {
      if (empty($job_id) || empty($sheetid))
         return null;
      $db = PFactory::getDbo();
      if ($is_mobile) {
         $select = " jsq.questionid AS id,jqd.question,jqd.answer AS options,js.id AS sheetid, jqt.type ";
      } else {
         $select = " jsq.questionid,jqd.question,jqd.question_img,jqd.answer,js.id AS sheetid, jqt.type ";
      }
      $query = "SELECT ".$select."
         FROM #__jobtest_question_details jqd
         LEFT JOIN #__jobtest_sheetquestions jsq ON jqd.questionid = jsq.questionid
         LEFT JOIN #__jobtest_sheet js ON js.id = jsq.sheetid
         LEFT JOIN #__jobtest_question jq ON jsq.questionid = jq.id
         LEFT JOIN #__jobtest_questiontype jqt ON jqt.id = jq.typeid
         LEFT JOIN #__jobtest_jobsheet jjs ON jjs.sheetid = js.id
         WHERE jjs.sheetid=".intval($sheetid)." AND jjs.jobid = ".intval($job_id)." AND jq.published = 1 AND js.published = 1 ORDER BY jq.typeid";
      $db->setQuery($query,$start,$limit);
      $paper = $db->loadObjectList();

      if (!$is_mobile) {
         $paper['last_end'] = $start+$limit;
      }

      foreach ($paper as $k => $ques) {
         if (!$is_mobile) {
            if (isset($ques->answer)) {
               $ques->answer = json_decode($ques->answer);
            }
         } else {
            if (isset($ques->options)) {
               $ques->options = json_decode($ques->options);
               $options = array();
               foreach ($ques->options as $kk => $vv) {
                  if (!empty($vv) && isset($vv->choice)) {
                     array_push($options, $vv->choice);
                  }
               }
               $ques->options = $options;
            }
         }
      }

      return $paper;
   }

   /**
    * Function postExam
    * @param  array $post      User's exam answers
    * @param  bool  $is_mobile Set true if called by mobile api
    * @return bool
    */
   public function postExam($post,$is_mobile=false) {
      if (!$is_mobile && !PSession::checkToken('post')) {
         $this->setError('验证已失效，请重新验证');
         return false;
      }

      if (empty($post)) {
         $this->setErrorCode(10002);
         $this->setError('网络异常，请重试！');
         return false;
      }

      // session may record failed
      /*if (empty($post['session_id'])) {
        $this->setError('网络异常，请重试！');
        return false;
      }*/

      $token = isset($post['token']) ? $post['token'] : "";
      $company_id = isset($post['employer_id']) ? $post['employer_id'] : 0;
      $jobseeker_id = isset($post['jobseeker_id']) ? $post['jobseeker_id'] : 0;
      $job_id = isset($post['job_id']) ? $post['job_id'] : 0;
      $sheetid = isset($post['sheetid']) ? $post['sheetid'] : 0;
      $session_id = isset($post['session_id']) ? $post['session_id'] : 0;
      if ($is_mobile) {
         $answers = isset($post['answers']) ? $post['answers'] : array();
      } else {
         $answers = $post;
      }

      $db = PFactory::getDbo();
      // check finish time
      $query = "SELECT totaltime FROM #__jobtest_sheet WHERE id = ".intval($sheetid);
      $db->setQuery($query);
      $totaltime = $db->loadResult();

      $query = "SELECT accept_date FROM *PREFIX*hr_jobseeker_token WHERE token = ".$db->quote($token);
      $db->setQuery(PUtil::replaceOCPrefix($query));
      $start_time = $db->loadResult();

      $late_time = time() - (PFactory::getDate($start_time)->toUnix() + $totaltime*60);

      // If late time is bigger than 3 mins, we think this answer is invalid
      if ($late_time > 180) {
         $this->setErrorCode(40019);
         $this->setError('已超过笔试规定时间，提交失败！');
         return false;
      }

      // Check if already taken this exam
      if ($this->hasTakeExamBefore($token)) {
         $this->setErrorCode(40008);
         $this->setError('您已参加过考试！');
         return false;
      }

      // update session registry now
      /*if(!$this->setAuthInfo($session_id, $token)) {
         $this->setErrorCode(10002);
         $this->setError('网络异常，请重试！');
         return false;
      }*/

      // unset some key , left answers only
      if (!$is_mobile) {
         unset($answers['token'],$answers['employer_id'],$answers['jobseeker_id'],$answers['job_id'],$answers['sheetid'],$answers['requesttoken'],$answers['session_id']);
      }

      if (empty($answers)) {
         $this->setErrorCode(40020);
         $this->setError('请完成您的答卷');
         return false;
      }

      // stay on master: read-your-write 
      $db->stayOnMaster();

      // update sheet numtesters
      $query = "UPDATE #__jobtest_jobsheet SET numtesters=numtesters+1 WHERE jobid=".$job_id;
      $db->setQuery($query);
      $db->execute();

      // get all questions correct answer
      $query = "SELECT jqd.questionid AS qid, jqd.answer, jsq.score, jqt.type
         FROM #__jobtest_sheet js
         LEFT JOIN #__jobtest_jobsheet jjs ON jjs.sheetid = js.id
         LEFT JOIN #__jobtest_sheetquestions jsq ON jsq.sheetid = js.id
         LEFT JOIN #__jobtest_question jq ON jq.id = jsq.questionid
         LEFT JOIN #__jobtest_question_details jqd ON jqd.questionid = jsq.questionid
         LEFT JOIN #__jobtest_questiontype jqt ON jqt.id = jq.typeid
         WHERE jjs.jobid = ".intval($job_id)." AND js.id=".intval($sheetid).
         " AND jq.published = 1";
      $db->setQuery($query);
      $questions = $db->loadObjectList();

      if (empty($questions)) {
         $this->setErrorCode(40021);
         $this->setError('出错了, 该公司没有设置笔试题');
         return false;
      }

      // Re-arrange the correct answer details
      $q_detail = array();
      foreach ($questions as $k => $question) {
         $q_detail[$question->qid] = array('score' => $question->score, 'type' => $question->type, 'correct' => array());
         $q_answers = json_decode($question->answer);

         if ($q_detail[$question->qid]['type'] == 'freeinput') {
            array_push($q_detail[$question->qid]['correct'], $q_answers[0]);
         } else {
            foreach ($q_answers as $key => $answer) {
               if ($answer->answer == 1) {
                  array_push($q_detail[$question->qid]['correct'], $key+1);
               }
            }
         }
      }

      $valuesArr = array();
      foreach ($q_detail as $qid => $detail) {
         // if jobseeker miss some questions, we think the score here is zero
         if (isset($answers[$qid])) {
            switch ($detail['type']) {
               case 'singleanswer':
                  if (in_array(intval($answers[$qid]), $detail['correct'])) {
                     $user_score = $detail['score'];
                     $user_answer = intval($answers[$qid]);
                  } else {
                     $user_score = '0';
                     $user_answer = intval($answers[$qid]);
                  }
                  break;
               case 'multipleanswer':
                  if ( count(array_diff($answers[$qid], $detail['correct'])) == 0 ) {
                     $user_score = $detail['score'];
                     $user_answer = implode(',', $answers[$qid]);
                  } else {
                     $user_score = '0';
                     $user_answer = implode(',', $answers[$qid]);
                  }
                  break;
               case 'freeinput':
                  $user_score = '0';
                  $user_answer = htmlspecialchars($answers[$qid]);
                  break;
               // singleanswer is default case
               default:
                  if (in_array(intval($answers[$qid]), $detail['correct'])) {
                     $user_score = $detail['score'];
                     $user_answer = intval($answers[$qid]);
                  } else {
                     $user_score = '0';
                     $user_answer = intval($answers[$qid]);
                  }
                  break;
            }
            $valuesArr[] = "('".intval($sheetid)."', '".intval($qid)."', '".intval($jobseeker_id)."', ".intval($user_score).", ".$db->quote($user_answer).")";
         } else {
            $valuesArr[] = "('".intval($sheetid)."', '".intval($qid)."', '".intval($jobseeker_id)."', '0', ' ')";
         }
      }

      $valuesSql = implode(",", $valuesArr);

      if (empty($valuesSql)) {
         $this->setErrorCode(10002);
         $this->setError('网络异常，请重试！');
         return false;
      }

      $query = "INSERT INTO #__jobtest_result
         (`sheetid`, `questionid`, `jobseeker_id`, `score`, `answer`)
         VALUES ".$valuesSql;
      $db->setQuery($query);
      if (!$db->execute()) {
         $db->leaveOnMaster();
         $this->setErrorCode(10002);
         $this->setError('网络异常，请重试！');
         return false;
      }

      // Update finished time
      $query = "UPDATE *PREFIX*hr_jobseeker_token SET finished_date = UTC_TIMESTAMP WHERE jobseeker_id =".intval($jobseeker_id);
      $db->setQuery(PUtil::replaceOCPrefix($query));
      if (!$db->execute()) {
         $db->leaveOnMaster();
         $this->setErrorCode(10002);
         $this->setError('网络异常，请重试！');
         return false;
      }
      CacheHelper::triggerRemove($jobseeker_id, array('*PREFIX*hr_jobseeker_token','*PREFIX*hr_jobseeker_token-'.$jobseeker_id));

      // Update total score
      $query = "UPDATE *PREFIX*hr_jobseeker SET online_interview_testscore = (SELECT SUM(score) FROM #__jobtest_result WHERE jobseeker_id = ".intval($jobseeker_id).") WHERE id=".intval($jobseeker_id);
      $db->setQuery(PUtil::replaceOCPrefix($query));
      if (!$db->execute()) {
         $db->leaveOnMaster();
         $this->setErrorCode(10002);
         $this->setError('网络异常，请重试！');
         return false;
      }
      CacheHelper::triggerRemove($jobseeker_id, array('*PREFIX*hr_jobseeker','*PREFIX*hr_jobseeker-'.$jobseeker_id));

      // Update matching level
      if (PInterviewHelper::updateMatchDegree($token, $jobseeker_id, $job_id) === false) {
         $db->leaveOnMaster();
         $this->setErrorCode(10002);
         $this->setError('网络异常，请重试！');
         return false;
      }

      /* update online_interview_progress; online test question: another 20% done; */
      PInterviewHelper::updateInterviewProgress('onlinetest', $jobseeker_id, $job_id);

      // Leave on master now 
      $db->leaveOnMaster();
      return true;
   }

   /**
    * Function checkInterviewStat
    * @param  string $token User token
    * @return bool
    */
   public function checkInterviewStat($token) {
      if (empty($token))
         return false;
      $db = PFactory::getDbo();
      $query = "SELECT COUNT(*)
         FROM *PREFIX*hr_jobseeker_interview hji
         LEFT JOIN *PREFIX*hr_jobseeker_token hjt ON hji.jobseeker_id = hjt.jobseeker_id
         WHERE hjt.token = ".$db->quote($token);
      $db->setQuery(PUtil::replaceOCPrefix($query));
      $result = $db->loadResult();

      if ($result > 0)
         return true;
      else
         return false;
   }

   /**
    * Function updateAccepted
    * @param  string $token  User token
    * @return bool
    *
    * Update user's accepted time, more than 3 times, return false
    */
   public function updateAccepted($token) {
      if (!$this->validateToken($token))
         return false;
      $accept_date = PFactory::getDate();

      $db = PFactory::getDbo();
      $update = "UPDATE *PREFIX*hr_jobseeker_token
         SET accepted=accepted+1, accept_date=".$db->quote($accept_date)."
         WHERE token = ".$db->quote($token);
      $db->setQuery(PUtil::replaceOCPrefix($update));
      return $db->execute();
   }

   /**
    * Function syncTimer
    * @param  integer $jstime       Js side left time
    * @param  string  $token        User token
    * @param  integer $totaltime    Total time of this sheet
    * @return integer or bool       If matched return true, otherwise return real time integer
    *
    * Sync jstime and backend time
    */
   public function syncTimer($jstime,$token,$totaltime) {
      if (empty($token) || empty($jstime) || empty($totaltime))
         return false;
      $db = PFactory::getDbo();
      $query = "SELECT accept_date
         FROM *PREFIX*hr_jobseeker_token
         WHERE token = ".$db->quote($token);
      $db->setQuery(PUtil::replaceOCPrefix($query));
      $start_time = $db->loadResult();

      $totaltime *= 60;
      $real_left = $totaltime - (time() - PFactory::getDate($start_time)->toUnix());

      if (abs($real_left - $jstime) > 60 ) {
         return $real_left;
      }

      return true;
   }

   /**
    * Function validateToken
    * @param  string  $token     This is what we get from url
    * @param  bool    $is_ajax   Is ajax mode?
    * @param  integer $chances    Num of chance to access a exam paper
    * @return bool
    *
    * If this token is not in our db, or this jobseeker has enter the page over 3 times
    * In that case we reject them and tell them why
    */
   public function validateToken($token,$is_ajax=false, $chances=3) {
      if (empty($token))
         return false;
      $db = PFactory::getDbo();
      $query = "SELECT accepted
         FROM *PREFIX*hr_jobseeker_token
         WHERE token = ".$db->quote($token);
      $db->setQuery(PUtil::replaceOCPrefix($query));
      $result = $db->loadResult();

      if ($is_ajax) {
         $chances += 1;
      }

      // if this token is not in our db, or this jobseeker has enter the page over 3 times
      // in that case we reject them and tell them why
      if ($result === null) {
         $this->setError("非法的请求");
         return false; 
      }
      if ($result >= $chances) {
         $this->setError("您的答题机会已用完！");
         return false; 
      }

      return true;
   }

   /**
    * Function setAuthInfo
    * @param  string $session_id    User's session_id
    * @param  string $token         User's token
    * @return bool
    */
   public function setAuthInfo($session_id, $token) {
      if (empty($session_id) || empty($token))
         return false;

      $db = PFactory::getDbo();
      $query = "SELECT hj.id,hj.name,hj.email
         FROM *PREFIX*hr_jobseeker_token hjt
         LEFT JOIN *PREFIX*hr_jobseeker hj ON hjt.jobseeker_id = hj.id
         WHERE hjt.token=".$db->quote($token);
      $db->setQuery(PUtil::replaceOCPrefix($query));
      $info = $db->loadObject();
      if (empty($info) || empty($info->id) || empty($info->name) || empty($info->email))
         return false;

      $pppweb = OC_Config::getValue('pipapai.com', 'http://yyjob.com');
      $data = array();
      $data['session_id'] = $session_id;
      $data['jobtester_id'] = $info->id;
      $data['jobtester_name'] = $info->name;
      $data['jobtester_email'] = $info->email;
      $httpclient = new JHttp2();
      $httpclient->post($pppweb . '/user/setsession', $data, null, false);
      return true;
   }

   /**
    * Function isPaperSet
    * @param  string   $token User's token
    * @return  boolean
    *
    * Did HR choose any sheet group ?
    */
   public function isPaperSet($token) {
      if (empty($token))
         return false;

      $db = PFactory::getDbo();
      $query = "SELECT COUNT(jjs.sheetid)
         FROM *PREFIX*hr_jobseeker_token hjt
         LEFT JOIN *PREFIX*hr_jobseeker hj ON hj.id = hjt.jobseeker_id
         LEFT JOIN #__jobtest_jobsheet jjs ON jjs.jobid = hj.job_id
         WHERE hjt.token=".$db->quote($token);
      $db->setQuery(PUtil::replaceOCPrefix($query));

      $result_str = $db->loadResult();

      if (!empty($result_str) && $result_str > 0) {
         return true;
      }
      return false;
   }

   /**
    * Function hasTakeExamBefore
    * @param  string  $token        User's token
    * @return boolean
    *
    * Check if this jobseeker has take test before
    */
   public function hasTakeExamBefore($token) {
      if (empty($token))
         return false;

      $db = PFactory::getDbo();
      $query = "SELECT COUNT(DISTINCT(jr.sheetid))
         FROM *PREFIX*hr_jobseeker_token hjt
         LEFT JOIN #__jobtest_result jr ON jr.jobseeker_id = hjt.jobseeker_id
         WHERE hjt.token=".$db->quote($token);
      $db->setQuery(PUtil::replaceOCPrefix($query));
      $taken_times = $db->loadResult();

      if (!empty($taken_times) && $taken_times > 0) {
         return true;
      }
      return false;
   }

   /**
    * Get jobseeker's job requirement
    *
    * @param  string $token  jobseeker's token
    * @return mixed
    *
    * @since 3.0
    */
   public function getJobReq($token) {
      if (empty($token))
         return null;
      $db = PFactory::getDbo();
      $query = "SELECT online_interview_requirements
               FROM *PREFIX*hr_jobseeker_token a
               LEFT JOIN *PREFIX*hr_jobseeker b ON b.id = a.jobseeker_id
               LEFT JOIN #__jobs_job c ON c.id = b.job_id
               WHERE a.token=".$db->quote($token);
      $db->setQuery(PUtil::replaceOCPrefix($query));
      $result = $db->loadResult();
      $result = $result ? json_decode($result) : null;
      return $result ? $result : null;
   }
   
   public function isGivenup($token){
      if (empty($token))
         return false;      
      $db = PFactory::getDbo();
      $query="SELECT giveup FROM *PREFIX*hr_jobseeker_token WHERE token=".$db->quote($token);
      $db->setQuery(PUtil::replaceOCPrefix($query));
      if($db->loadResult()==1){
         return true;
      }    
      return false ;
   }
   
   public function giveupConfirm() {
      if(!PSession::checkToken('post')) {
         $this->setError('登录已失效，请重新登录');
         return false;
      }
      $token = JRequest::getString('token', '');
      $email = JRequest::getString('email', '');
      $jobseeker_tag=JRequest::getString('reason','');

      $db = PFactory::getDbo();

      $query = "SELECT company_id FROM *PREFIX*hr_jobseeker_token WHERE token = ".$db->quote($token);
      $db->setQuery(PUtil::replaceOCPrefix($query));
      if($db->execute())
         $company_id=$db->loadResult();

      // Check email owner
      $query = "SELECT hjt.jobseeker_id as id,hj.job_id as jobid
               FROM *PREFIX*hr_jobseeker hj
               LEFT JOIN *PREFIX*hr_jobseeker_token hjt ON hj.id=hjt.jobseeker_id 
               WHERE hjt.token=".$db->quote($token)." AND hj.email = ".$db->quote($email);
      $db->setQuery(PUtil::replaceOCPrefix($query));
      $result = $db->loadObject();
      if(!$result)
         return false;

      // Get jobseeker's tags
      $this->insertTags($jobseeker_tag,$company_id,$result->id);

      // Once jobseeker commits give up reason,update oc_hr_jobseeker_token's give up info
      $query = "UPDATE *PREFIX*hr_jobseeker_token SET giveup=1,giveup_date=UTC_TIMESTAMP() where token=".$db->quote($token);
      $db->setQuery(PUtil::replaceOCPrefix($query));
      $db->execute();

      PResumesHelper::setResumeStatus($result->id,PCalendarHelper::JOBSEEKER_STATUS_GIVEUP);

      // stay on master 
      $db->stayOnMaster();
      // Update jobseeker's online_interview_progress's interview equal 0
      PInterviewHelper::updateInterviewProgress('interview',$result->id,$result->jobid,true);

      // leave on master 
      $db->leaveOnMaster(); 

      return true;
   }

   function insertTags($name, $owner,$jobseekerid, $validReasons=array()) {
      if(empty($name) || !$owner)
         return null;
      $tag = $name;
      if ( empty($validReasons) ) {
         $validReasons = self::$validGiveUpReasons;
      }
      if(!in_array($name, $validReasons))
         $tag = "其他原因放弃";
      $db = PFactory::getDbo();
      // For any tags , the verified is default to 1
      $verified = 1;
      $query="INSERT  INTO *PREFIX*hr_jobseeker_tag_term (name, owner, verified, created) VALUES ";
      $query .= "( " . $db->quote($tag) . ", " . intval($owner) . ", " . $verified . ", UTC_TIMESTAMP()) ON DUPLICATE KEY UPDATE id=LAST_INSERT_ID(id)";
      $db->setQuery(PUtil::replaceOCPrefix($query));
      $db->execute();
      $insert_tagid=$db->insertid();
      //give up reason only get last reason
      $query="DELETE FROM *PREFIX*hr_jobseeker_tag_term_details WHERE jobseeker_id=".intval($jobseekerid). " AND owner=".intval($owner) . " AND tagid IN (SELECT id FROM *PREFIX*hr_jobseeker_tag_term WHERE name IN ('".implode("','",$validReasons)."') AND owner=".intval($owner).")";
      $db->setQuery(PUtil::replaceOCPrefix($query));
      $db->execute();
      $query="INSERT IGNORE INTO *PREFIX*hr_jobseeker_tag_term_details (tagid, owner, jobseeker_id, added) VALUES ";
      $query .= "( " . intval($insert_tagid) . ", " . intval($owner) . ", " . intval($jobseekerid) . ", UTC_TIMESTAMP())";
      $db->setQuery(PUtil::replaceOCPrefix($query));
      $db->execute();
   }
   
   
}
